package org.iteam.core.service.sys;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.sql.DataSource;

import org.iteam.core.Constant;
import org.iteam.core.Constant.Status;
import org.iteam.core.model.sys.SysAuth;
import org.iteam.core.model.sys.SysRole;
import org.iteam.core.model.sys.SysUser;
import org.iteam.core.service.BaseService;
import org.iteam.core.service.BaseServiceInterface;
import org.iteam.core.service.DBService;
import org.iteam.core.utils.SqlBuilder;
import org.iteam.core.view.sys.AuthView;
import org.iteam.core.view.sys.SysPermissionView;

public abstract class SysUserService extends BaseService<SysUser> implements
		BaseServiceInterface<SysUser> {
	private static SysUserService serviceMysql = null;
	private static SysUserService serviceOracle = null;

	public static SysUserService getInstance() {
		Constant.DataBaseType dbTypes = Constant.dataBaseType;
		if (dbTypes.equals(Constant.DataBaseType.MYSQL)) {
			return getMySqlInstance();
		} else if (dbTypes.equals(Constant.DataBaseType.ORACLE)) {
			return getOracleInstance();
		}
		return null;
	}

	public SysUserService() {
		super(SysUser.class);
	}

	public Set<String> getPermissions(String account,
			Collection<String> roleNames) {
		String sql = "SELECT A.AUTH_ROLE AS PERMISSION_ROLE, D.* FROM SYS_AUTH A INNER JOIN SYS_ROLE B ON A.AUTH_ROLE = B. CODE INNER JOIN SYS_PERMISSION C ON C.PERMISSION_ROLE = A.AUTH_ROLE INNER JOIN SYS_FUNCTION D ON D. CODE = C.PERMISSION_FUNCTION WHERE B. STATUS = ? AND D. STATUS = ? AND A.AUTH_USER = ?";

		Set<String> strings = new HashSet<String>();
		try {
			List<SysPermissionView> list = super.query(SysPermissionView.class,
					sql, Constant.Status.ENABLE.ecode,
					Constant.Status.ENABLE.ecode, account);
			for (SysPermissionView sf : list) {
				strings.add(sf.getPermission_role() + "_" + sf.getAuth());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return strings;
	}

	public abstract List<SysPermissionView> getPermissionsAll();

	public List<AuthView> querySub(String account) throws SQLException {
		String sql = "SELECT B.AUTH_ROLE, B.AUTH_USER,B.PRI, A.* FROM SYS_ROLE A LEFT JOIN ( SELECT * FROM SYS_AUTH C WHERE C.AUTH_USER = ? ) B ON A. CODE = B.AUTH_ROLE WHERE A. STATUS = ?";
		return super.query(AuthView.class, sql, account,
				Constant.Status.ENABLE.ecode);
	}

	public List<SysRole> getRolesByUser(String account) throws SQLException {
		String sql = "SELECT A.* FROM SYS_ROLE A INNER JOIN SYS_AUTH B ON B.AUTH_ROLE = A. CODE WHERE B.AUTH_USER = ? AND  A.STATUS = ? ORDER BY B.PRI";
		return super.query(SysRole.class, sql, account,
				Constant.Status.ENABLE.ecode);
	}

	public abstract boolean updateUserRole(String account, String role,
			boolean _permission) throws SQLException;

	public boolean updateUserRoleDefault(String account, String role)
			throws SQLException {
		SqlBuilder sb = SqlBuilder.createUpdate(SysAuth.TABLE_NAME);
		sb.addField("pri", 1);
		sb.addWhere("auth_user", account);
		Connection conn = null;
		try {
			DataSource dataSource = DBService.getDataSource();
			conn = dataSource.getConnection();
			conn.setAutoCommit(false);
			super.update(conn, sb.getSql().toUpperCase(), sb.getParams());
			sb = SqlBuilder.createUpdate(SysAuth.TABLE_NAME);
			sb.addField("pri", 0);
			sb.addWhere("auth_role", role);
			sb.addWhere("auth_user", account);
			if (super.update(conn, sb.getSql().toUpperCase(), sb.getParams())) {
				conn.commit();
				return true;
			} else {
				conn.rollback();
			}
			return false;
		} catch (Exception e) {
			conn.rollback();
			throw new SQLException(e.getMessage());
			// TODO Auto-generated catch block
		} finally {
			if (conn != null) {
				conn.close();
			}
		}

	}

	public Set<String> getRoleNamesForUser(String account) {
		Set<String> strings = new HashSet<String>();
		try {
			List<SysRole> list = getRolesByUser(account);
			for (SysRole sysAuth : list) {
				strings.add(sysAuth.getCode());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return strings;
	}

	public static SysUserService getMySqlInstance() {
		if (serviceMysql == null)
			serviceMysql = new org.iteam.core.service.mysql.impl.sys.SysUserServiceImpl();
		return serviceMysql;
	}

	public static SysUserService getOracleInstance() {
		if (serviceOracle == null)
			serviceOracle = new org.iteam.core.service.oracle.impl.sys.SysUserServiceImpl();
		return serviceOracle;
	}

	public List<SysUser> query() throws SQLException {
		SqlBuilder sb = SqlBuilder.createQuery(SysUser.TABLE_NAME, "*");
		return super.query(sb.getSql().toUpperCase());
	}

	public long count() throws SQLException {
		SqlBuilder sb = SqlBuilder.createQuery(SysUser.TABLE_NAME, "*");
		return Long.parseLong(super.certain(sb.getMaxCountSql().toUpperCase())
				.toString());
	}

	public long validate(String account) throws SQLException {
		SqlBuilder sb = SqlBuilder.createQuery(SysUser.TABLE_NAME, "*");
		sb.addWhere("account", account);
		return Long.parseLong(super.certain(sb.getMaxCountSql().toUpperCase(),account)
				.toString());
	}

	public SysUser show(String id) throws SQLException {
		SqlBuilder sb = SqlBuilder.createQuery(SysUser.TABLE_NAME, "*");
		sb.addWhere("account", id);
		return super.show(sb.getSql().toUpperCase(), id);
	}

	public abstract boolean create(SysUser obj) throws SQLException;

	public boolean update(SysUser obj) throws SQLException {
		SqlBuilder sb = SqlBuilder.createUpdate(SysUser.TABLE_NAME);
		sb.addField("name", obj.getName());
		sb.addField("alias", obj.getAlias());
		sb.addField("status", obj.getStatus());
		sb.addWhere("account", obj.getAccount());
		return super.update(sb.getSql().toUpperCase(), sb.getParams());
	}

	public boolean reset(String account, String newPwd) throws SQLException {
		SqlBuilder sb = SqlBuilder.createUpdate(SysUser.TABLE_NAME);
		sb.addField("password", newPwd);
		sb.addWhere("account", account);
		return super.update(sb.getSql().toUpperCase(), sb.getParams());
	}

	public boolean delete(String id) throws SQLException {
		SqlBuilder sb = SqlBuilder.createUpdate(SysUser.TABLE_NAME);
		sb.addField("status", Status.DELETE.ecode);
		sb.addWhere("account", id);
		return super.update(sb.getSql().toUpperCase(), sb.getParams());
	}

	public boolean destroy(String id) throws SQLException {
		SqlBuilder sb = SqlBuilder.createDelete(SysUser.TABLE_NAME);
		sb.addWhere("account", id);
		return super.update(sb.getSql().toUpperCase(), sb.getParams());
	}

	public boolean resetPwd(String id, String newPwd) throws SQLException {
		SqlBuilder sb = SqlBuilder.createUpdate(SysUser.TABLE_NAME);
		sb.addField("password", newPwd);
		sb.addWhere("account", id);
		return super.update(sb.getSql().toUpperCase(), sb.getParams());
	}

	public boolean updatePwd(String id, String olePwd, String newPwd)
			throws SQLException {
		SqlBuilder sb = SqlBuilder.createUpdate(SysUser.TABLE_NAME);
		sb.addField("password", newPwd);
		sb.addWhere("account", id);
		sb.addWhere("password", olePwd);
		return super.update(sb.getSql().toUpperCase(), sb.getParams());
	}
}
